package com.deng.etl.excel.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelRule;
import com.deng.util.DateTools;

public class YaoFangFaYaoImpl extends SQLHelper implements IExcelRule {


	public void importData(Workbook wb, String type_name,String id) {
		try{			
				Connection conn = getConnect();
				
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
				StringBuffer sb = new StringBuffer();
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					Cell c2 = sh.getCell(1,i);
					if(sb.indexOf("'"+(getYM1(c1.getContents(),c2.getContents()))+"',") == -1){
						sb.append("'"+(getYM1(c1.getContents(),c2.getContents()))+"',");
					}
				}
				String delSQL = "delete t_org_pharmacy_workload_month" +
						"	where stat_time in ("+sb.toString().substring(0, sb.length()-1)+")";
				updateData(conn, delSQL);//删除stat_Time当天
				
				conn.setAutoCommit(false);
				
				
				String insertSQL = "insert into t_org_pharmacy_workload_month " +
						"	(stat_time,create_time," +
						"org_operate_organization_id,org_operate_organization_name," +
						"count)" +//发药次数
						"	values(?,'"+DateTools.dtsWithTime(new Date())+"'," +
						"?,?,?)";
				PreparedStatement pst =conn.prepareStatement(insertSQL);
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);
					Cell c3 = sh.getCell(2,i);
					Cell c4 = sh.getCell(3,i);
					Cell c5 = sh.getCell(4,i);
					
					Date stat_time = DateTools.stdJustDate(getYM1(c1.getContents(),c2.getContents()));
					String org_operate_organization_id = c3.getContents();
					String org_operate_organization_name = c4.getContents();
					Integer count = Integer.valueOf(c5.getContents()!=null?c5.getContents().trim():"0");
					
					pst.setDate(1, new java.sql.Date(stat_time.getTime()));
					pst.setString(2, org_operate_organization_id);
					pst.setString(3, org_operate_organization_name);
					pst.setInt(4, count);
					pst.addBatch();
										
				}
				pst.executeBatch();
				conn.commit();
				pst.close();
				freeConnection(conn);

		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	private String getYM1(String y,String m){
		return y.trim()+"-"+m.trim()+"-1";
	}
}
